package com.liang.spring.test.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import com.liang.spring.test.controller.entity.BuyTicketInfo;
import com.liang.spring.test.controller.entity.BuyTicketInfoExample;
import com.liang.spring.test.controller.entity.TrainInfo;
import com.liang.spring.test.controller.entity.TrainInfoExample;
import com.liang.spring.test.controller.entity.BuyTicketInfoExample.Criteria;
import com.liang.spring.test.controller.entity.UserInfo;
import com.liang.spring.test.dao.BuyTicketInfoMapper;
import com.liang.spring.test.dao.TrainInfoMapper;

@Controller
@RequestMapping(value = "file")
public class fileController {

	@Autowired
	private TrainInfoMapper trainInfoMapper;

	/*
	 * @RequestMapping(value = "/uploadXlsfile") public void uploadFile(Model
	 * model, HttpServletRequest request, HttpSession session) { try {
	 * MultipartHttpServletRequest multipartRequest
	 * =(MultipartHttpServletRequest) request; //SpringMVC上传文件的控件
	 * //提取;文件，这个参数必须和上传控件的name一致。返回上传内容对象，如果不存在就返回null.
	 * 
	 * MultipartFile multipartFile = multipartRequest.getFile("file");//注意名称和
	 * 上传控件的名称一致
	 * 
	 * InputStream in = multipartFile.getInputStream();//获取控件内容的输入流
	 * 
	 * 
	 * } catch (Exception e) { e.printStackTrace(); } finally {
	 * 
	 * } }
	 */

	@RequestMapping(value = "/uploadXlsfile", method = RequestMethod.POST)
	public String fileUpload(HttpServletRequest request, HttpServletResponse response, Model model) throws Exception {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		MultipartFile multipartFile = multipartRequest.getFile("trainFile");
		String fileName = multipartFile.getOriginalFilename();
		String filePath = request.getParameter("txtFile");
		int rows = 0;
		HSSFSheet sh = null;
		XSSFSheet xh = null;
		TrainInfo traininfo = null;
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		boolean blnReturn = false;
		try {
			if (fileName.trim().length() == 0) {
				model.addAttribute("msg", "请选择文件！");
				blnReturn = true;
			} 
			if (blnReturn) {
				return "/train";
			}
			InputStream is07 = new FileInputStream(filePath);
			XSSFWorkbook xwk = null;
			try {
				xwk = new XSSFWorkbook(is07);
			} catch (Exception e) {
				model.addAttribute("msg", "文件格式不正确！");
				return "/train";
			}
			xh = xwk.getSheetAt(0);
			rows = xh.getLastRowNum() + 1 - xh.getFirstRowNum(); 			 // 总行数
			for (int i = 1; i < rows; i++) {
				XSSFRow row = xh.getRow(i);
				int cols = row.getLastCellNum() - row.getFirstCellNum(); 	// 该行的总列数
				String[] str = new String[cols]; 						 	// 用来存放该行每一列的值
				for (int j = 0; j < cols; j++) {
					Object col = row.getCell((short) j);
					str[j] = col.toString();
					Cell cell = row.getCell(j);  
					int type = cell.getCellType();
					switch (type) {
					case Cell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) { 		// 处理日期格式、时间格式
							str[j] = simpleDateFormat.format(cell.getDateCellValue());
							break;
						}
					default:
						continue;
					}
				}
				try {
					traininfo = new TrainInfo();
					traininfo.setTrainnumber(str[0]);
					traininfo.setStarttime(simpleDateFormat.parse(str[1]));
					traininfo.setEndtime(simpleDateFormat.parse(str[2]));
					traininfo.setputtickettime(simpleDateFormat.parse(str[3]));
					traininfo.setStartstation(str[4]);
					traininfo.setEndstation(str[5]);
					traininfo.setViastaion1(str[6].equalsIgnoreCase("无") ? "" : str[6]);
					traininfo.setViastaion2(str[7].equalsIgnoreCase("无") ? "" : str[7]);
					traininfo.setViastaion3(str[8].equalsIgnoreCase("无") ? "" : str[8]);
					traininfo.setViastaion4(str[9].equalsIgnoreCase("无") ? "" : str[9]);
					traininfo.setViastaion5(str[10].equalsIgnoreCase("无") ? "" : str[10]);
					traininfo.setPrice(Double.parseDouble(str[20]));
					trainInfoMapper.deleteByPrimaryKey(str[0]);
					trainInfoMapper.insert(traininfo);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			model.addAttribute("msg", "上传成功！");
		} catch (Exception e) {
			InputStream is03 = new FileInputStream(filePath);
			HSSFWorkbook hwk = null;	// 将is流实例到 一个excel流里
			try {
				hwk = new HSSFWorkbook(is03);
			} catch (Exception ee) {
				model.addAttribute("msg", "文件格式不正确！");
				return "/train";
			}
			sh = hwk.getSheetAt(0); 							// 得到book第一个工作薄sheet
			rows = sh.getLastRowNum() + 1 - sh.getFirstRowNum(); // 总行数
			for (int i = 0; i < rows; i++) {
				HSSFRow row = sh.getRow(i);
				int cols = row.getLastCellNum() - row.getFirstCellNum(); 	// 该行的总列数
				String[] str = new String[cols]; 						 	// 用来存放该行每一列的值
				for (int j = 0; j < cols; j++) {
					Object col = row.getCell((short) j);
					str[j] = col.toString();
					Cell cell = row.getCell(j);  
					int type = cell.getCellType();
					switch (type) {
					case Cell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) { 		// 处理日期格式、时间格式
							str[j] = simpleDateFormat.format(cell.getDateCellValue());
							break;
						}
					default:
						continue;
					}
				}
				try {
					traininfo = new TrainInfo();
					traininfo.setTrainnumber(str[0]);
					traininfo.setStarttime(simpleDateFormat.parse(str[1]));
					traininfo.setEndtime(simpleDateFormat.parse(str[2]));
					traininfo.setputtickettime(simpleDateFormat.parse(str[3]));
					traininfo.setStartstation(str[4]);
					traininfo.setEndstation(str[5]);
					traininfo.setViastaion1(str[6].equalsIgnoreCase("无") ? "" : str[6]);
					traininfo.setViastaion2(str[7].equalsIgnoreCase("无") ? "" : str[7]);
					traininfo.setViastaion3(str[8].equalsIgnoreCase("无") ? "" : str[8]);
					traininfo.setViastaion4(str[9].equalsIgnoreCase("无") ? "" : str[9]);
					traininfo.setViastaion5(str[10].equalsIgnoreCase("无") ? "" : str[10]);
					traininfo.setPrice(Double.parseDouble(str[20]));
					trainInfoMapper.deleteByPrimaryKey(str[0]);
					trainInfoMapper.insert(traininfo);
				} catch (Exception ee) {
					ee.printStackTrace();
				}
			}
			model.addAttribute("msg", "上传成功！");
		}
		return "/train";
	}

	/*
	 * ModelAndView uploadView = new ModelAndView(); // 创建文件保存目录
	 * SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd/HH");
	 * String path = "/shaomch/files/" + dateformat.format(new Date()); String
	 * realPath = request.getSession().getServletContext().getRealPath(path); //
	 * 根据真实路径创建目录 File saveFile = new File(realPath); if (!saveFile.exists()) {
	 * saveFile.mkdirs(); } MultipartHttpServletRequest multipartRequest =
	 * (MultipartHttpServletRequest) request; MultipartFile multipartFile =
	 * multipartRequest.getFile("file"); // 获得文件后缀 String suffix =
	 * multipartFile.
	 * getOriginalFilename().substring(multipartFile.getOriginalFilename
	 * ().indexOf(".")); // 生成文件名 String fileName = UUID.randomUUID().toString()
	 * + suffix; String fullFileName = realPath + File.separator + fileName;
	 * File file = new File(fullFileName); try { multipartFile.transferTo(file);
	 * } catch (Exception ex) { ex.printStackTrace(); }
	 * uploadView.addObject("fileMessage", fullFileName);
	 * uploadView.setViewName("imgload");
	 */
}
